﻿CREATE PROCEDURE dbo.SP_MemberCommitties_SELECT
@StartIndex INT = NULL,
@lenght INT =  NULL ,
@OrderColume nvarchar(256) = NULL
, @Church NVarChar (256)  =  NULL 
, @CommittieID Int   =  NULL 
, @DayOfMonth TinyInt   =  NULL 
, @DayOfWeek TinyInt   =  NULL 
, @IsServant Bit   =  NULL 
, @MemberId Int   =  NULL 
, @Name NVarChar (256)  =  NULL 
, @OccurreEveryMonths TinyInt   =  NULL 
, @OccurreEveryWeeks Int   =  NULL 
, @OccurrenceType TinyInt   =  NULL 
, @ServantType NVarChar (256)  =  NULL 
, @TimeOfWeek DateTime   =  NULL 
, @WeekOfMonth TinyInt   =  NULL 
	AS
			WITH CTE_MemberCommitties AS
			( 
			SELECT
 							Church,
								CommittieID,
								DayOfMonth,
								DayOfWeek,
								IsServant,
								MemberId,
								Name,
								OccurreEveryMonths,
								OccurreEveryWeeks,
								OccurrenceType,
								ServantType,
								TimeOfWeek,
								WeekOfMonth,
							ROW_NUMBER() OVER (ORDER BY 
        	CASE 
			 
						WHEN @OrderColume='Church' THEN Church
												WHEN @OrderColume='CommittieID' THEN CommittieID
												WHEN @OrderColume='DayOfMonth' THEN DayOfMonth
												WHEN @OrderColume='DayOfWeek' THEN DayOfWeek
												WHEN @OrderColume='IsServant' THEN IsServant
												WHEN @OrderColume='MemberId' THEN MemberId
						 
						WHEN @OrderColume='Name' THEN Name
												WHEN @OrderColume='OccurreEveryMonths' THEN OccurreEveryMonths
												WHEN @OrderColume='OccurreEveryWeeks' THEN OccurreEveryWeeks
												WHEN @OrderColume='OccurrenceType' THEN OccurrenceType
						 
						WHEN @OrderColume='ServantType' THEN ServantType
												WHEN @OrderColume='TimeOfWeek' THEN TimeOfWeek
												WHEN @OrderColume='WeekOfMonth' THEN WeekOfMonth
									ELSE 
							    CommittieID
					        	END
			) AS "RowNumber"
  			FROM MemberCommitties 
			where 
								(Church LIKE @Church OR @Church Is Null)
										AND(CommittieID = @CommittieID OR @CommittieID Is Null)
										AND(DayOfMonth = @DayOfMonth OR @DayOfMonth Is Null)
										AND(DayOfWeek = @DayOfWeek OR @DayOfWeek Is Null)
										AND(IsServant = @IsServant OR @IsServant Is Null)
										AND(MemberId = @MemberId OR @MemberId Is Null)
										AND(Name LIKE @Name OR @Name Is Null)
										AND(OccurreEveryMonths = @OccurreEveryMonths OR @OccurreEveryMonths Is Null)
										AND(OccurreEveryWeeks = @OccurreEveryWeeks OR @OccurreEveryWeeks Is Null)
										AND(OccurrenceType = @OccurrenceType OR @OccurrenceType Is Null)
										AND(ServantType LIKE @ServantType OR @ServantType Is Null)
										AND(TimeOfWeek = @TimeOfWeek OR @TimeOfWeek Is Null)
										AND(WeekOfMonth = @WeekOfMonth OR @WeekOfMonth Is Null)
								)
			SELECT  *
			FROM CTE_MemberCommitties
			WHERE  (RowNumber >= @StartIndex OR @StartIndex Is Null) AND (RowNumber <= @StartIndex+@lenght OR (@StartIndex Is Null and @lenght Is Null))
			Return (select @@ROWCOUNT)
			